package kr.co.doriclassic.workhischool.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.co.doriclassic.mylibrary.DbUtil;
import kr.co.doriclassic.workhischool.dto.Student;


public class StudentDAO implements InterfaceDAO<Student> {
	private Connection con;
	
	public StudentDAO(Connection con) {
		this.con = con;
	}
	@Override
	public void insertItem(Student item) throws SQLException {
		String sql1="insert into student values(?,?)";
		String sql2="insert into s_score values(?,?,?,?)";
		PreparedStatement pstmt1 = con.prepareStatement(sql1);
		pstmt1.setInt(1, item.getStdno());
		pstmt1.setString(2, item.getStdname());
		PreparedStatement pstmt2 = con.prepareStatement(sql2);
		pstmt2.setInt(1, item.getStdno());
		pstmt2.setInt(2, item.getKor());
		pstmt2.setInt(3, item.getEng());
		pstmt2.setInt(4, item.getMath());
		pstmt1.executeUpdate(sql1);
		pstmt2.executeUpdate(sql2);
		DbUtil.close(pstmt1);
		DbUtil.close(pstmt2);
	}

	@Override
	public ArrayList<Student> listItem() throws SQLException {
		ArrayList<Student> lists = new ArrayList<>();
		String sql="select student.stdno, stdname, kor, eng, math from student, s_score";
		PreparedStatement pstmt = con.prepareStatement(sql); 
		ResultSet rs = pstmt.executeQuery(sql);
		while(rs.next()){
			lists.add(new Student(rs.getInt("stdno"), rs.getString("stdname"), rs.getInt("kor"), rs.getInt("eng"), rs.getInt("math")));
		}
		return lists;
	}
}
